﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Data.Common;
using System.IO;

namespace Faust.Andrew.MTG.Migration
{
    class SqlExpressToSQLite
    {
        public void Migrate(string sourceSQLExpress, string destSQLite)
        {
            using (System.Data.SqlClient.SqlConnection sqlConnection = new SqlConnection(sourceSQLExpress))
            {
                sqlConnection.Open();

                using (SQLiteConnection liteConnection = new SQLiteConnection(GetLiteConnectionString(destSQLite)))
                {
                    liteConnection.Open();

                    ConvertCards(sqlConnection, liteConnection);
                }
            }
        }

        private void ConvertCards(SqlConnection sqlConnection, SQLiteConnection liteConnection)
        {
            using (SqlCommand sourceCommand = new SqlCommand())
            {
                sourceCommand.Connection = sqlConnection;
                sourceCommand.CommandText = "select * from Cards";

                using (SqlDataReader reader = sourceCommand.ExecuteReader())
                {
                    using (SQLiteTransaction transaction = liteConnection.BeginTransaction())
                    {
                        while (reader.Read())
                        {
                            using (SQLiteCommand command = new SQLiteCommand())
                            {
                                command.Connection = liteConnection;
                                command.Transaction = transaction;
                                command.CommandText = "insert into cards ([MultiverseID], [Rarity], [Toughness], [ToughnessNumeric], [Power], [PowerNumeric], "
                                    + "[CardTypes], [ConvertedManaCost], [ConvertedManaCostNumeric], [Name], [Expansion], [SubType], [CardText], [ManaCost], [Quantity], [Color], [Image]) values ("
                                    + "@MultiverseID, @Rarity, @Toughness, @ToughnessNumeric, @Power, @PowerNumeric, "
                                    + "@CardTypes, @ConvertedManaCost, @ConvertedManaCostNumeric, @Name, @Expansion, @SubType, @CardText, @ManaCost, @Quantity, @Color, @Image)";

                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    command.Parameters.AddWithValue(String.Format("@{0}", reader.GetName(i)), reader[i]);
                                }

                                command.ExecuteNonQuery();
                            }
                        }

                        transaction.Commit();
                    }
                }
            }
        }

        private string GetLiteConnectionString(string litePath)
        {
            return String.Format("Data Source={0}; Version=3;", litePath);
        }
    }
}
